﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
using QuanLyThuPhiTaiLieu.Common;
using QuanLyThuPhiTaiLieu.Entity;
using QuanLyThuPhiTaiLieu.Model;
using Excel = Microsoft.Office.Interop.Excel;

namespace QuanLyThuPhiTaiLieu.Biz
{
    public class PhieuBanGiaoTaiLieuBiz : BaseBiz
    {
        public int SaveItem(PhieuBanGiaoTaiLieu item)
        {
            var dbContext = new Context();

            dbContext.PhieuBanGiaoTaiLieus.Add(item);
            int newId = dbContext.SaveChanges();

            return newId;
        }

        public void UpdateItem(PhieuBanGiaoTaiLieu item)
        {
            var dbContext = new Context();
            dbContext.Entry(item).State = EntityState.Modified;
            dbContext.SaveChanges();
        }

        public List<PhieuBanGiaoTaiLieu> ViewAll()
        {
            var dbContext = new Context();
            return dbContext.Database.SqlQuery<PhieuBanGiaoTaiLieu>("PhieuBanGia_TongTien").ToList().OrderBy(item => item.SoPhieu).ToList();
        }

        public PhieuBanGiaoTaiLieu LoadItem(int id)
        {
            var dbContext = new Context();
            return (from item in dbContext.PhieuBanGiaoTaiLieus
                    where item.Id == id
                    select item).First();
        }

        public void RemoveItem(List<PhieuBanGiaoTaiLieu> items)
        {
            var dbContext = new Context();
            foreach (var tailieu in items)
            {
                dbContext.PhieuBanGiaoTaiLieus.Attach(tailieu);
                dbContext.PhieuBanGiaoTaiLieus.Remove(tailieu);
                dbContext.Entry(tailieu).State = EntityState.Deleted;
            }

            dbContext.SaveChanges();
        }

        public void ExportBang(int id, string fileOutPath = "", bool isPrint = false)
        {
            PhieuBanGiaoTaiLieu item = LoadItem(id);

            const int usedSheet = 1;            
                        
            string filePath = ConfigurationManager.AppSettings["TemplateBangPath"];            
            
            Excel.Application xlApp = null;
            Excel.Workbook xlWorkbook = null;
            string openFile = string.Empty;
            try
            {
                xlApp = new Excel.Application();
                xlWorkbook = xlApp.Workbooks.Open(filePath);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[usedSheet];

                string pageSetup = ConfigurationManager.AppSettings["PageSetup"];

                if (string.Equals("1", pageSetup, StringComparison.InvariantCultureIgnoreCase))
                {
                    xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                }
                else
                {
                    xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
                }              

                int firstRow = 6;

                // Ho ten
                Excel.Range rangeSoPhieu = xlWorksheet.Range["F2", "H2"];
                rangeSoPhieu.Merge();
                rangeSoPhieu.Value2 = string.Concat("Số phiếu: ", item.SoPhieu);

                // Ho ten
                Excel.Range rangeNguoiNhan = xlWorksheet.Range["A" + firstRow, "C" + firstRow];
                rangeNguoiNhan.Merge();
                rangeNguoiNhan.Value2 = string.Concat("Họ và tên người nhận: ", item.NguoiNhan);
                rangeNguoiNhan.get_Characters(1, 20).Font.Bold = true;

                // Dien Thoai
                Excel.Range rangeSoDienThoai = xlWorksheet.Range["E" + firstRow, "H" + firstRow];
                rangeSoDienThoai.Merge();
                rangeSoDienThoai.Value2 = string.Concat("Số điện thoại: ", item.DienThoai);                
                
                firstRow++;

                // Chuc Danh
                Excel.Range rangeChucDanh = xlWorksheet.Range["A" + firstRow, "H" + firstRow];
                rangeChucDanh.Merge();
                rangeChucDanh.Value2 = string.Concat("Chức danh: ", Convert.ToString(item.ChucDanh));

                firstRow++;

                // Don Vi
                Excel.Range rangeDonVi = xlWorksheet.Range["A" + firstRow, "H" + firstRow];
                rangeDonVi.Merge();
                rangeDonVi.Value2 = string.Concat("Đơn vị: ", Convert.ToString(item.DonVi));

                firstRow++;

                // Dia Chi
                Excel.Range rangeDiaChi = xlWorksheet.Range["A" + firstRow, "H" + firstRow];
                rangeDiaChi.Merge();
                rangeDiaChi.Value2 = string.Concat("Địa chỉ: ", Convert.ToString(item.DiaChi));

                firstRow++;

                // Giay gioi thieu
                Excel.Range rangeThongTinChiTiet = xlWorksheet.Range["A" + firstRow, "H" + firstRow];
                rangeThongTinChiTiet.Merge();
                rangeThongTinChiTiet.Value2 = string.Concat("Số giấy giới thiệu/CV: ", Convert.ToString(item.ThongTinChiTiet));
                rangeThongTinChiTiet.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                rangeThongTinChiTiet.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                rangeThongTinChiTiet.WrapText = true;
                rangeThongTinChiTiet.RowHeight = 44;

                firstRow++;

                // Hinh thuc thanh toan
                Excel.Range rangeHinhThucThanhToan = xlWorksheet.Range["A" + firstRow, "H" + firstRow];
                rangeHinhThucThanhToan.Merge();
                rangeHinhThucThanhToan.Value2 = string.Concat("Hình thức thanh toán: ", Convert.ToString(item.HinhThucThanhToan));

                ExportDetailBang(xlWorksheet, id);

                if (isPrint)
                {                    
                    xlWorkbook.PrintOutEx(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                
                }
                else
                {
                    xlWorkbook.SaveAs(fileOutPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
                    openFile = fileOutPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (xlWorkbook != null)
                {
                    xlWorkbook.Close(false, Missing.Value, Missing.Value);
                }
                if (xlApp != null)
                {
                    xlApp.Quit();
                }
            }
            if (!string.IsNullOrEmpty(openFile))
            {
                Process.Start(openFile);
            }
        }

        public void ExportPhieu(int id, string fileOutPath = "", bool isPrint = false)
        {
            PhieuBanGiaoTaiLieu item = LoadItem(id);

            const int usedSheet = 1;

            string filePath = ConfigurationManager.AppSettings["TemplatePhieuPath"];

            Excel.Application xlApp = null;
            Excel.Workbook xlWorkbook = null;

            string openFile = string.Empty;
            try
            {
                xlApp = new Excel.Application();
                xlWorkbook = xlApp.Workbooks.Open(filePath);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[usedSheet];

                string pageSetup = ConfigurationManager.AppSettings["PageSetup"];

                if (string.Equals("1", pageSetup, StringComparison.InvariantCultureIgnoreCase))
                {
                    xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                }
                else
                {
                    xlWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
                }                

                int firstRow = 7;

                Excel.Range rangeNguoiNhan = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeNguoiNhan.Merge();
                rangeNguoiNhan.Value2 = string.Concat("Họ và tên người nhận: ", item.NguoiNhan);
                rangeNguoiNhan.get_Characters(1, 20).Font.Bold = true;

                firstRow++;
                Excel.Range rangeChucDanh = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeChucDanh.Merge();
                rangeChucDanh.Value2 = string.Concat("Chức danh: ", Convert.ToString(item.ChucDanh));

                firstRow++;
                Excel.Range rangeDonVi = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeDonVi.Merge();
                rangeDonVi.Value2 = string.Concat("Đơn vị: ", Convert.ToString(item.DonVi));

                firstRow++;
                Excel.Range rangeDiaChi = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeDiaChi.Merge();
                rangeDiaChi.Value2 = string.Concat("Địa chỉ: ", Convert.ToString(item.DiaChi));

                firstRow++;
                Excel.Range rangeThongTinChiTiet = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeThongTinChiTiet.Merge();
                rangeThongTinChiTiet.Value2 = string.Concat("Số giấy giới thiệu/CV: ", Convert.ToString(item.ThongTinChiTiet));
                rangeThongTinChiTiet.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                rangeThongTinChiTiet.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                rangeThongTinChiTiet.WrapText = true;
                rangeThongTinChiTiet.RowHeight = 44;

                firstRow++;
                Excel.Range rangeHinhThucThanhToan = xlWorksheet.Range["A" + firstRow, "I" + firstRow];
                rangeHinhThucThanhToan.Merge();
                rangeHinhThucThanhToan.Value2 = string.Concat("Hình thức thanh toán: ", Convert.ToString(item.HinhThucThanhToan));

                ExportDetailPhieu(xlWorksheet, id);

                if (isPrint)
                {
                    xlWorkbook.PrintOutEx(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                    
                }
                else
                {
                    xlWorkbook.SaveAs(fileOutPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
                    openFile = fileOutPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (xlWorkbook != null)
                {
                    xlWorkbook.Close(false, Missing.Value, Missing.Value);
                }
                if (xlApp != null)
                {
                    xlApp.Quit();
                }
            }

            if (!string.IsNullOrEmpty(openFile))
            {
                Process.Start(openFile);
            }
        }        

        public void ExportDetailPhieu(Excel._Worksheet sheet, int parentId)
        {
            var biz = new PhieuBanGiaoTaiLieuChiTietBiz();
            var items = biz.LoadItemsByParentId(parentId);            

            int index = 15;
            int lastRow = 15;
            decimal total = 0;

            int firstRow = 15;
            string currentGroup = string.Empty;
            string group = string.Empty;
            int groupIndexValue = 1;
            int itemIndexValue = 1;
            int lastGroupIndex = 0;
            string totalSum = "=Sum(";

            string maTaiLieuValue = string.Empty;
            string maBaoCaoValue = string.Empty;            
            
            for (int i = 0; i < items.Count; i++)
            {                
                var item = items[i];

                currentGroup = Convert.ToString(item.GroupValue);

                if (string.IsNullOrEmpty(group) || !string.Equals(currentGroup, group, StringComparison.InvariantCultureIgnoreCase))
                {
                    if (!string.IsNullOrEmpty(group))
                    {
                        Excel.Range rowLastHeader = sheet.Rows[index - itemIndexValue];
                        rowLastHeader.Cells[1, 9].Value2 = string.Format("=sum(I{0}:I{1})", index - itemIndexValue + 1, index - 1);
                        totalSum += string.Format("I{0},", index - itemIndexValue);
                                                
                        if (string.Equals(rowLastHeader.Cells[1, 4].Value2, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                        {
                            rowLastHeader.Cells[1, 4].Value2 = maBaoCaoValue;
                        }
                    }

                    group = currentGroup;

                    Excel.Range rowHeader = sheet.Rows[index];
                    rowHeader.Cells[1, 1].Value2 = groupIndexValue;
                    rowHeader.Cells[1, 2].Value2 = Convert.ToString(item.Khlt);
                    rowHeader.Cells[1, 3].Value2 = Convert.ToString(item.TenBaoCao);
                    rowHeader.Cells[1, 4].Value2 = Convert.ToString(item.MaBaoCao);

                    if (!string.Equals(item.MaBaoCao, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                    {
                        maBaoCaoValue = item.MaBaoCao;
                    }

                    rowHeader.Cells[1, 5].Value2 = string.Empty;
                    rowHeader.Cells[1, 6].Value2 = string.Empty;
                    rowHeader.Cells[1, 7].Value2 = string.Empty;
                    rowHeader.Cells[1, 8].Value2 = string.Empty;
                    rowHeader.Cells[1, 9].Value2 = string.Empty;
                    rowHeader.Font.Bold = true;
                    lastGroupIndex = index;
                    itemIndexValue = 1;
                    index++;
                    groupIndexValue++;
                }
                else                
                {
                    if (!string.Equals(item.MaBaoCao, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                    {
                        maBaoCaoValue = item.MaBaoCao;
                    }
                }

                Excel.Range row = sheet.Rows[index];
                row.Cells[1, 1].Value2 = string.Concat("'", groupIndexValue - 1, ".", itemIndexValue);
                row.Cells[1, 2].Value2 = string.Empty;
                row.Cells[1, 3].Value2 = Convert.ToString(item.TenTaiLieu);
                row.Cells[1, 4].Value2 = Convert.ToString(item.MaTaiLieu) + item.MucThu.Trim();
                row.Cells[1, 5].Value2 = string.Concat("'", Convert.ToString(item.NoiDungYeuCau));
                row.Cells[1, 6].Value2 = Convert.ToString(item.DonViTinh);
                row.Cells[1, 7].Value2 = Convert.ToString(item.SoLuong);
                row.Cells[1, 8].Value2 = Convert.ToString(item.DonGia);
                row.Cells[1, 9].Value2 = Convert.ToString(item.ThanhTien);
                total += item.ThanhTien;
                index++;
                itemIndexValue++;
                lastRow++;
            }

            Excel.Range rowFinishHeader = sheet.Rows[index - itemIndexValue];
            rowFinishHeader.Cells[1, 9].Value2 = string.Format("=sum(I{0}:I{1})", index - itemIndexValue + 1, index - 1);
            if (string.Equals(rowFinishHeader.Cells[1, 4].Value2, "TKTL", StringComparison.InvariantCultureIgnoreCase))
            {
                rowFinishHeader.Cells[1, 4].Value2 = maBaoCaoValue;
            }
            totalSum += string.Format("I{0})", index - itemIndexValue);            

            //Excel.Range cellFullInfo = sheet.Range["A" + firstRow, "H" + firstRow];
            //cellFullInfo.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            Excel.Range cellStt = sheet.Range["A" + firstRow, "B" + index];
            cellStt.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellStt.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            cellStt.NumberFormat = "@";

            Excel.Range cellTenTaiLieu = sheet.Range["C" + firstRow, "C" + index];
            cellTenTaiLieu.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            cellTenTaiLieu.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            cellTenTaiLieu.WrapText = true;

            Excel.Range cellDanhMuc = sheet.Range["D" + firstRow, "F" + index];
            cellDanhMuc.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellDanhMuc.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            cellDanhMuc.NumberFormat = "@";
            cellDanhMuc.WrapText = true;

            Excel.Range cellNoiDung = sheet.Range["G" + firstRow, "G" + index];
            cellNoiDung.NumberFormat = "@";
            cellNoiDung.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellNoiDung.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            Excel.Range cellNumber = sheet.Range["H" + firstRow, "I" + index];
            cellNumber.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            cellNumber.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            cellNumber.NumberFormat = "#,###";

            Excel.Range cellTotal = sheet.Range["A" + index, "H" + index];
            cellTotal.Merge();
            cellTotal.Value2 = "Tổng tiền:";
            cellTotal.Font.Bold = true;
            cellTotal.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;            

            Excel.Range cellTotalValue = sheet.Range["I" + index];
            if (index > firstRow)
            {
                cellTotalValue.Formula = totalSum;
                cellTotalValue.Font.Bold = true;
            }
            index++;

            Excel.Range cellBangChu = sheet.Range["A" + index, "I" + index];
            cellBangChu.Merge();
            string money = Utilities.ConvertCurrency(total, 1);
            cellBangChu.Value2 = "Bằng chữ: " + money;
            cellBangChu.Font.Italic = true;

            Excel.Range cellChiTiet = sheet.Range["A" + firstRow, "I" + index];
            cellChiTiet.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            cellChiTiet.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            cellChiTiet.Borders.Color = Color.Black;

            index += 2;

            string data = @"Tài liệu nhận phải được sử dụng đúng mục đích, không cung cấp tài liệu cho bên không liên quan đến nhiệm vụ. Nếu vi phạm, bên nhận tài liệu phải chịu hoàn toàn trách nhiệm trước pháp luật. Phiếu này được lập thành 02 bản có giá trị như nhau, bên giao giữ 01 bản, bên nhận giữ 01 bản.";
            Excel.Range cellInfo = sheet.Range["H" + index];
            Excel.Range cellInfoValue = sheet.Range["A" + index, "I" + index];
            cellInfoValue.Merge();
            cellInfoValue.Value2 = data;
            cellInfoValue.WrapText = true;
            cellInfoValue.RowHeight = 46;

            index++;

            string dataSign1 = @"NGƯỜI GIAO                       NGƯỜI NHẬN                       TP.LTĐC                           GIÁM ĐỐC";
            Excel.Range cellSign1 = sheet.Range["I" + index];
            Excel.Range cellSignValue1 = sheet.Range["A" + index, "I" + index];
            cellSignValue1.Merge();
            cellSignValue1.Value2 = dataSign1;
            cellSignValue1.WrapText = true;

            index++;
            string dataSign2 = @"Ký và ghi rõ họ tên                  Ký và ghi rõ họ tên                  Ký và ghi rõ họ tên                Ký và ghi rõ họ tên";            
            Excel.Range cellSign2 = sheet.Range["I" + index];
            Excel.Range cellSignValue2 = sheet.Range["A" + index, "I" + index];
            cellSignValue2.Merge();
            cellSignValue2.Value2 = dataSign2;
            cellSignValue2.WrapText = true;            
        }

        public void ExportDetailBang(Excel._Worksheet sheet, int parentId)
        {
            var biz = new PhieuBanGiaoTaiLieuChiTietBiz();
            var items = biz.LoadItemsByParentId(parentId);

            int index = 14;
            int lastRow = 14;
            decimal total = 0;

            int firstRow = 14;
            string currentGroup = string.Empty;
            string group = string.Empty;
            int groupIndexValue = 1;
            int itemIndexValue = 1;
            int lastGroupIndex = 0;
            string totalSum = "=Sum(";

            string maBaoCaoValue = string.Empty;   

            for (int i = 0; i < items.Count; i++)
            {
                var item = items[i];

                currentGroup = Convert.ToString(item.GroupValue);

                if (string.IsNullOrEmpty(group) || !string.Equals(currentGroup, group, StringComparison.InvariantCultureIgnoreCase))
                {
                    if (!string.IsNullOrEmpty(group))
                    {
                        Excel.Range rowLastHeader = sheet.Rows[index - itemIndexValue];
                        rowLastHeader.Cells[1, 8].Value2 = string.Format("=sum(H{0}:H{1})", index - itemIndexValue + 1, index - 1);
                        totalSum += string.Format("H{0},", index - itemIndexValue);

                        if (string.Equals(rowLastHeader.Cells[1, 4].Value2, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                        {
                            rowLastHeader.Cells[1, 4].Value2 = maBaoCaoValue;
                        }
                    }

                    group = currentGroup;

                    Excel.Range rowHeader = sheet.Rows[index];
                    rowHeader.Cells[1, 1].Value2 = groupIndexValue;
                    rowHeader.Cells[1, 2].Value2 = Convert.ToString(item.Khlt);
                    rowHeader.Cells[1, 3].Value2 = Convert.ToString(item.TenBaoCao);
                    rowHeader.Cells[1, 4].Value2 = Convert.ToString(item.MaBaoCao);

                    if (!string.Equals(item.MaBaoCao, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                    {
                        maBaoCaoValue = item.MaBaoCao;
                    }

                    rowHeader.Cells[1, 5].Value2 = string.Empty;
                    rowHeader.Cells[1, 6].Value2 = string.Empty;
                    rowHeader.Cells[1, 7].Value2 = string.Empty;
                    rowHeader.Cells[1, 8].Value2 = string.Empty;
                    rowHeader.Font.Bold = true;
                    lastGroupIndex = index;
                    itemIndexValue = 1;
                    index++;
                    groupIndexValue++;

                }
                else
                {
                    if (!string.Equals(item.MaBaoCao, "TKTL", StringComparison.InvariantCultureIgnoreCase))
                    {
                        maBaoCaoValue = item.MaBaoCao;
                    }
                }

                Excel.Range row = sheet.Rows[index];
                row.Cells[1, 1].Value2 = string.Concat(groupIndexValue - 1, ".", itemIndexValue);
                row.Cells[1, 2].Value2 = string.Empty;
                row.Cells[1, 3].Value2 = Convert.ToString(item.TenTaiLieu);
                row.Cells[1, 4].Value2 = Convert.ToString(item.MaTaiLieu) + item.MucThu.Trim();
                row.Cells[1, 5].Value2 = Convert.ToString(item.DonViTinh);
                row.Cells[1, 6].Value2 = Convert.ToString(item.SoLuong);
                row.Cells[1, 7].Value2 = Convert.ToString(item.DonGia);
                row.Cells[1, 8].Value2 = Convert.ToString(item.ThanhTien);
                total += item.ThanhTien;
                index++;
                itemIndexValue++;
                lastRow++;
            }

            Excel.Range rowFinishHeader = sheet.Rows[index - itemIndexValue];
            rowFinishHeader.Cells[1, 8].Value2 = string.Format("=sum(H{0}:H{1})", index - itemIndexValue + 1, index - 1);
            if (string.Equals(rowFinishHeader.Cells[1, 4].Value2, "TKTL", StringComparison.InvariantCultureIgnoreCase))
            {
                rowFinishHeader.Cells[1, 4].Value2 = maBaoCaoValue;
            }
            totalSum += string.Format("H{0})", index - itemIndexValue);
            
            Excel.Range cellStt = sheet.Range["A" + firstRow, "B" + index];
            cellStt.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellStt.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            cellStt.NumberFormat = "@";

            Excel.Range cellTenTaiLieu = sheet.Range["C" + firstRow, "C" + index];
            cellTenTaiLieu.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            cellTenTaiLieu.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            cellTenTaiLieu.WrapText = true;

            Excel.Range cellMa = sheet.Range["D" + firstRow, "F" + index];
            cellMa.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellMa.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;            
            
            Excel.Range cellNoiDung = sheet.Range["F" + firstRow, "F" + index];
            cellNoiDung.NumberFormat = "@";
            cellNoiDung.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            cellNoiDung.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;            

            Excel.Range cellNumber = sheet.Range["G" + firstRow, "H" + index];
            cellNumber.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            cellNumber.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;            
            cellNumber.NumberFormat = "#,###";

            Excel.Range cellTotal = sheet.Range["A" + index, "G" + index];
            cellTotal.Merge();
            cellTotal.Value2 = "Tổng tiền:";
            cellTotal.Font.Bold = true;
            cellTotal.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;            

            Excel.Range cellTotalValue = sheet.Range["H" + index];
            if (index > firstRow)
            {
                cellTotalValue.Formula = totalSum;
                cellTotalValue.Font.Bold = true;
            }

            index++;
                
            Excel.Range cellBangChu = sheet.Range["A" + index, "H" + index];
            cellBangChu.Merge();
            string money = Utilities.ConvertCurrency(total, 1);
            cellBangChu.Value2 = "Bằng chữ: " + money;
            cellBangChu.Font.Italic = true;

            Excel.Range cellChiTiet = sheet.Range["A" + firstRow, "H" + index];
            cellChiTiet.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            cellChiTiet.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            cellChiTiet.Borders.Color = Color.Black;

            index += 2;

            Excel.Range rangeDate = sheet.Range["E" + index, "H" + index];
            rangeDate.Merge();
            rangeDate.Value2 = "Ngày       tháng           năm 20";

            index += 1;

            Excel.Range rangeNguoiLap = sheet.Range["A" + index, "B" + index];
            rangeNguoiLap.Merge();
            rangeNguoiLap.Value2 = "P.Lưu trữ                             Người lập bảng";

            Excel.Range rangeNguoiNop = sheet.Range["E" + index, "H" + index];
            rangeNguoiNop.Merge();
            rangeNguoiNop.Value2 = "Người nộp tiền";
            rangeNguoiNop.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;   
        }

        public List<string> GetAllNguoiNhan()
        {
            var dbContext = new Context();
            return (from item in dbContext.PhieuBanGiaoTaiLieus
                    select item.NguoiNhan).Distinct().ToList();
        }

        public List<string> GetAllDonVi()
        {
            var dbContext = new Context();
            return (from item in dbContext.PhieuBanGiaoTaiLieus
                    select item.DonVi).Distinct().ToList();
        }

        public List<string> GetAllNam()
        {
            var dbContext = new Context();
            return (from item in dbContext.PhieuBanGiaoTaiLieus
                    where item.Nam.HasValue
                    select item.Nam.Value).Distinct().ToList().Select(n => Convert.ToString(n)).ToList();
        }

        public List<int> GetByParam(string nguoiNhan, string donVi, int? nam)
        {
            if (nam.HasValue)
            {
                var dbContext = new Context();
                return (from item in dbContext.PhieuBanGiaoTaiLieus
                        where
                            (item.NguoiNhan == nguoiNhan || string.IsNullOrEmpty(nguoiNhan)) &&
                            (item.DonVi == donVi || string.IsNullOrEmpty(donVi)) &&
                            (item.Nam.Value == nam.Value)
                        select item.Id).Distinct().ToList();
            }
            else
            {
                var dbContext = new Context();
                return (from item in dbContext.PhieuBanGiaoTaiLieus
                        where
                            (item.NguoiNhan == nguoiNhan || string.IsNullOrEmpty(nguoiNhan)) &&
                            (item.DonVi == donVi || string.IsNullOrEmpty(donVi))                            
                        select item.Id).Distinct().ToList();
            }
        }
    }
}
